﻿Imports AugmentLib.Database
Imports System.Configuration.ConfigurationManager
Partial Class state
    Inherits System.Web.UI.Page

#Region " Properties "

    Public Property CurrentPage() As Integer
        Get
            'Looks for the current page in ViewState
            Dim objPage As Object = ViewState("_CurrentPage")
            If objPage Is Nothing Then
                Return 0
            Else
                'default to showing the first page
                Return CInt(objPage)
            End If
        End Get
        Set(ByVal value As Integer)
            ViewState("_CurrentPage") = value
        End Set
    End Property

    Public Property PageSize() As Integer
        Get
            'Looks for the current page in ViewState
            Dim objPageSize As Object = ViewState("_PageSize")
            If objPageSize Is Nothing Then
                Return 25
            Else
                'default to showing the first page
                Return CInt(objPageSize)
            End If
        End Get
        Set(ByVal value As Integer)
            ViewState("_PageSize") = value
        End Set
    End Property

#End Region

#Region " Paging and Page Size "

    Protected Sub lnkHead10Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkHead10Item.Click
        PageSize = 10
        LoadState()
    End Sub

    Protected Sub lnkHead25Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkHead25Item.Click
        PageSize = 25
        LoadState()
    End Sub

    Protected Sub lnkHead100Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkHead100Item.Click
        PageSize = 100
        LoadState()
    End Sub

    Protected Sub lnkHead150Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkHead150Item.Click
        PageSize = 150
        LoadState()
    End Sub

    Protected Sub lnkFoot10Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkFoot10Item.Click
        PageSize = 10
        LoadState()
    End Sub

    Protected Sub lnkFoot25Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkFoot25Item.Click
        PageSize = 25
        LoadState()
    End Sub

    Protected Sub lnkFoot100Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkFoot100Item.Click
        PageSize = 100
        LoadState()
    End Sub

    Protected Sub lnkFoot150Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkFoot150Item.Click
        PageSize = 150
        LoadState()
    End Sub

    Protected Sub btnMoveFirst_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnMoveFirst.Click
        CurrentPage = 0
        txtPageNo.Text = CurrentPage + 1
       LoadState()
    End Sub

    Protected Sub btnPrevious_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
        If CurrentPage > 0 Then
            CurrentPage = CurrentPage - 1
            txtPageNo.Text = CurrentPage + 1
           LoadState()
        End If
    End Sub

    Protected Sub btnGo_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGo.Click
        If CInt(txtPageNo.Text < 1) Then
            txtPageNo.Text = 1
        End If

        If CInt(txtPageNo.Text > CInt(lblMaxPageCount.Text)) Then
            txtPageNo.Text = CInt(lblMaxPageCount.Text) + 1
        End If

        CurrentPage = CInt(txtPageNo.Text) - 1
      LoadState()

    End Sub

    Protected Sub btnNext_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnNext.Click
        If CurrentPage < CInt(lblMaxPageCount.Text) Then
            CurrentPage = CurrentPage + 1
            txtPageNo.Text = CurrentPage + 1
           LoadState()
        End If
    End Sub

    Protected Sub btnMoveLast_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnMoveLast.Click
        CurrentPage = CInt(lblMaxPageCount.Text)
        txtPageNo.Text = CurrentPage + 1
        LoadState()
    End Sub

#End Region

    Private Sub LoadState()

        Dim CountryFilter As String = String.Empty
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        Dim TempDT As New Data.DataTable

        objDB.OpenConnection()

        'to add in filtering for country
        'edited by minginn @ 28/6/2011 - to load the listing according to search criteria after edit but not after save
        'If strCountry <> "" Then
        '    CountryFilter = " AND s.CountryId = '" & strCountry & "'"
        'End If

        If ddlCountryFilter.SelectedValue <> "" Then
            CountryFilter = " AND s.CountryId = '" & ddlCountryFilter.SelectedValue & "'"
        End If

        If txtSearch.Text = "" Then
            objDB.dbVariable.SQLString = "SELECT StateId,s.CountryId,State,Country FROM AW_State s " & _
                                        "INNER JOIN AW_Country c ON s.CountryId = c.CountryId " & _
                                        "WHERE s.StatusDelete=0" & CountryFilter & lblHidSortType.Text
            objDB.FillData(TempDT, "AW_State", Database.SQLCommandType.NormalString)
        Else
            objDB.dbVariable.SQLString = "SELECT StateId,s.CountryId,State,Country FROM AW_State s " & _
                                        "INNER JOIN AW_Country c ON s.CountryId = c.CountryId " & _
                                        "WHERE s.StatusDelete=0 AND State LIKE @State" & CountryFilter & lblHidSortType.Text
            objDB.AddParameter("State", Data.SqlDbType.NVarChar, "%" & txtSearch.Text.Trim & "%")
            objDB.FillData(TempDT, "AW_State", Database.SQLCommandType.NormalString)
        End If
        objDB.CloseConnection()

        'Creating a Paged Datasouce
        Dim objPaged As New PagedDataSource
        objPaged.DataSource = TempDT.DefaultView
        objPaged.AllowPaging = True
        objPaged.PageSize = PageSize
        objPaged.CurrentPageIndex = CurrentPage

        lblHeadNoOfItem.Text = TempDT.Rows.Count
        lblFootNoOfItem.Text = TempDT.Rows.Count

        repState.DataSource = objPaged
        repState.DataBind()

        lblMaxPageCount.Text = objPaged.PageCount - 1
        lblTotalPage.Text = " of " & objPaged.PageCount

    End Sub

    Protected Sub imgNewState_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles imgNewState.Click
        Getddl()
        ClearAllField()
        mpeEntry.Show()
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Session("StaffId") Is Nothing Then
            Response.Redirect("Default.aspx")
        ElseIf Session.Item("StaffType").ToString = "2" Then
            Response.Redirect("Default.aspx")
        Else
            lblstaffUID.Text = Session("StaffId").ToString
        End If

        If Not Page.IsPostBack Then
            GetddlFilter()
            LoadState()
            'Added by : Daniel Lee
            'Date : 22-Apr-2011
            'Purpose : To auto update each other when typing the search criteria
            txtSearch.Attributes.Add("onKeyUp", "SearchReplace('" & txtSearch.ClientID & "','" & txtFilterSearch.ClientID & "')")
            txtSearch.Attributes.Add("onPaste", "SearchReplace('" & txtSearch.ClientID & "','" & txtFilterSearch.ClientID & "')")
            txtFilterSearch.Attributes.Add("onKeyUp", "SearchReplace('" & txtFilterSearch.ClientID & "','" & txtSearch.ClientID & "')")
            txtFilterSearch.Attributes.Add("onPaste", "SearchReplace('" & txtFilterSearch.ClientID & "','" & txtSearch.ClientID & "')")
        End If
    End Sub

    Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        ddlCountryFilter.SelectedIndex = 0
        'Removed By :  Daniel Lee
        'Date : 22-April-2011
        'Purpose : Moved this code into javascript
        'txtFilterSearch.Text = txtSearch.Text.Trim
        LoadState()
    End Sub

    Protected Sub repState_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.RepeaterCommandEventArgs) Handles repState.ItemCommand
        If e.CommandName.ToUpper = "EDITITEM" Then
            Getddl()
            txtState.Text = CType(repState.Items(e.CommandArgument).FindControl("lblRepState"), Label).Text
            ddlCountry.SelectedValue = CType(repState.Items(e.CommandArgument).FindControl("lblRepCountryUID"), Label).Text
            lblStateUID.Text = CType(repState.Items(e.CommandArgument).FindControl("lblRepStateUID"), Label).Text
            mpeEntry.Show()
        Else
            'Begin Delete
            Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))

            objDB.OpenConnection()
            objDB.dbVariable.SQLString = "UPDATE AW_State SET StatusDelete=1 WHERE StateId=@StateId"
            objDB.AddParameter("StateId", Data.SqlDbType.UniqueIdentifier, New Guid(CType(repState.Items(e.CommandArgument).FindControl("lblRepStateUID"), Label).Text))
            objDB.ExecuteQuery()
            objDB.CloseConnection()
            lblMsgSuccessful.Text = "State Deleted"
            lblMsgSuccessful.Visible = True
          LoadState()
        End If
    End Sub

    Private Sub ClearAllField()
        txtState.Text = String.Empty
        ddlCountry.SelectedIndex = 0
        lblStateUID.Text = String.Empty
        divError.Visible = False
        divSuccessful.Visible = False
    End Sub

    Private Sub ClearFilterField()
        txtSearch.Text = String.Empty
        txtFilterSearch.Text = String.Empty
        ddlCountryFilter.SelectedIndex = 0
    End Sub

    Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click

        If lblStateUID.Text = "" Then
            'New
            Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))

            objDB.OpenConnection()
            Dim ExistState As String
            ExistState = StateExist(objDB)
            If ExistState = "N" Then
                ClearAllField()
                ClearFilterField()
                divError.Visible = True
                lblMsgError.Text = "State Already Exist"
                LoadState()
            ElseIf ExistState <> "" Then
                objDB.dbVariable.SQLString = "UPDATE AW_State SET StatusDelete=@StatusDelete WHERE StateId=@StateId"
                objDB.AddParameter("StateId", Data.SqlDbType.UniqueIdentifier, New Guid(ExistState))
                objDB.AddParameter("StatusDelete", Data.SqlDbType.TinyInt, 0)
                objDB.ExecuteQuery()
                objDB.CloseConnection()
                ClearAllField()
                ClearFilterField()
                divSuccessful.Visible = True
                lblMsgSuccessful.Text = "State Successfully Updated"
                LoadState()
            Else
                objDB.dbVariable.SQLString = "INSERT INTO AW_State (StateId,CountryId,State,CreateDate,StatusDelete) VALUES (@StateId,@CountryId,@State,@CreateDate,@StatusDelete)"
                objDB.AddParameter("StateId", Data.SqlDbType.UniqueIdentifier, Guid.NewGuid)
                objDB.AddParameter("CountryId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlCountry.SelectedValue))
                objDB.AddParameter("State", Data.SqlDbType.NVarChar, txtState.Text.Trim.ToUpper)
                objDB.AddParameter("CreateDate", Data.SqlDbType.DateTime, Now)
                objDB.AddParameter("StatusDelete", Data.SqlDbType.TinyInt, 0)
                objDB.ExecuteQuery()
                ClearAllField()
                ClearFilterField()
                divSuccessful.Visible = True
                lblMsgSuccessful.Text = "State Successfully Inserted"
                LoadState()
            End If
            objDB.CloseConnection()
        Else
            'Edit
            Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))

            objDB.OpenConnection()
            objDB.dbVariable.SQLString = "UPDATE AW_State SET State=@State, CountryId=@CountryId WHERE StateId=@StateId"
            objDB.AddParameter("StateId", Data.SqlDbType.UniqueIdentifier, New Guid(lblStateUID.Text))
            objDB.AddParameter("CountryId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlCountry.SelectedValue))
            objDB.AddParameter("State", Data.SqlDbType.NVarChar, txtState.Text.Trim.ToUpper)
            objDB.ExecuteQuery()
            objDB.CloseConnection()
            ClearAllField()
            divSuccessful.Visible = True
            lblMsgSuccessful.Text = "State Successfully Updated"
            LoadState()
        End If
    End Sub

    Private Function StateExist(ByRef objDB As Database) As String
        Dim Tempdt As New Data.DataTable
        Dim strState As String

        objDB.dbVariable.SQLString = "SELECT State, StateId, StatusDelete FROM AW_State WHERE CountryId=@CountryId AND State=@State"
        objDB.AddParameter("CountryId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlCountry.SelectedValue))
        objDB.AddParameter("State", Data.SqlDbType.NVarChar, txtState.Text.Trim.ToUpper)
        objDB.FillData(Tempdt, "AW_State", Database.SQLCommandType.NormalString)
        objDB.ClearParameter()
        If Tempdt.Rows.Count > 0 Then
            For Each row As Data.DataRow In Tempdt.Rows
                If row.Item("StatusDelete").ToString = "0" Then
                    strState = "N"
                Else
                    strState = row.Item("StateId").ToString
                End If
            Next
        Else
            strState = ""
        End If
        Return strState
    End Function

    Private Sub Getddl()
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        objDB.dbVariable.SQLString = "SELECT CountryId,Country FROM AW_Country WHERE StatusDelete=0 ORDER BY Country"
        objDB.FillData(TempDT, "Country", Database.SQLCommandType.NormalString)
        With ddlCountry
            .DataSource = TempDT
            .DataTextField = "Country"
            .DataValueField = "CountryId"
            .DataBind()
            .Items.Insert(0, "-SELECT-")
            .Items(0).Value = ""
        End With

        TempDT.Clear()

        objDB.CloseConnection()

        TempDT.Clear()
    End Sub

    Private Sub GetddlFilter()
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        objDB.dbVariable.SQLString = "SELECT CountryId,Country FROM AW_Country WHERE StatusDelete=0 ORDER BY Country"
        objDB.FillData(TempDT, "Country", Database.SQLCommandType.NormalString)
        With ddlCountryFilter
            .DataSource = TempDT
            .DataTextField = "Country"
            .DataValueField = "CountryId"
            .DataBind()
            .Items.Insert(0, "ANY")
            .Items(0).Value = ""
        End With

        TempDT.Clear()

        objDB.CloseConnection()

        TempDT.Clear()
    End Sub

    Protected Sub btnSearchFilter_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearchFilter.Click
        txtSearch.Text = txtFilterSearch.Text.Trim
        LoadState()
    End Sub

    Protected Sub btnResetSearchFilter_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnResetSearchFilter.Click
        txtFilterSearch.Text = String.Empty
        txtSearch.Text = String.Empty
        ddlCountryFilter.SelectedIndex = 0
    End Sub

    Protected Sub ddlCountryFilter_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCountryFilter.SelectedIndexChanged
        LoadState()
    End Sub

    Private Function SortType(ByVal strSortType As String) As String
        Dim strSort As String
        If Right(strSortType, 1) = "▼" Then
            strSort = "ASC"
        Else
            strSort = "DESC"
        End If
        Return strSort
    End Function

    Protected Sub lnkState_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkState.Click
        If lnkState.Text = "State▼" Then
            lnkState.Text = "State▲"
        Else
            lnkState.Text = "State▼"
        End If

        lblHidSortType.Text = " ORDER BY State " & SortType(lnkState.Text) & ", Country " & SortType(lnkCountry.Text)
        LoadState()
    End Sub

    Protected Sub lnkCountry_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkCountry.Click
        If lnkCountry.Text = "Country▼" Then
            lnkCountry.Text = "Country▲"
        Else
            lnkCountry.Text = "Country▼"
        End If

        lblHidSortType.Text = " ORDER BY Country " & SortType(lnkCountry.Text) & ", State " & SortType(lnkState.Text)
        LoadState()
    End Sub

    Protected Sub repState_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles repState.ItemDataBound

        If e.Item.ItemType = ListItemType.AlternatingItem Or e.Item.ItemType = ListItemType.Item Then
            CType(e.Item.FindControl("cbeDelete"), Object).confirmText = "Are you sure you want to delete " & CType(e.Item.FindControl("lblRepState"), Label).Text
        End If

    End Sub
End Class
